package com.eam.handler;

import com.basis.utils.BaseServletHandler;
import com.basis.utils.CodeGenerateRuleUtil;
import com.eam.data.*;
import com.mes.data.*;
import org.apache.commons.fileupload.FileItem;
import org.apache.commons.fileupload.disk.DiskFileItemFactory;
import org.apache.commons.fileupload.servlet.ServletFileUpload;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.codehaus.jettison.json.JSONObject;
import org.openbravo.dal.service.OBDal;
import org.openbravo.database.SessionInfo;
import org.openbravo.model.ad.access.User;
import org.openbravo.model.common.enterprise.Organization;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

/*
 * 保养计划导入
 */
public class MaintainPlanImportHandler  extends BaseServletHandler {
	
	private static SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");//处理时间格式

	@Override
	protected JSONObject service(HttpServletRequest request, HttpServletResponse response) {
		JSONObject result = new JSONObject();
		try {
			result.put("desc", "系统繁忙，请稍后再试。");
			result.put("result", false);
			request.setCharacterEncoding("utf-8");
			DiskFileItemFactory factory = new DiskFileItemFactory();
			// 设置 缓存的大小，当上传文件的容量超过该缓存时，直接放到 暂时存储室
			factory.setSizeThreshold(1024 * 1024);
			ServletFileUpload upload = new ServletFileUpload(factory);
			// 可以上传多个文件
			List<FileItem> listItem = (List<FileItem>) upload.parseRequest(request);
			for (FileItem item : listItem) {
				if (!item.isFormField()) {
					String name = item.getName();
					if (item.getSize() > 3 * 1024 * 1024) {
						result.put("desc", "文件大小不能超过3M");
						return result;
					}
					InputStream is = item.getInputStream(); // 文件流
					try {
						Workbook wb = null;
						if (name.endsWith(".xls")) {
							wb = new HSSFWorkbook(is);
						} else if (name.endsWith(".xlsx")) {
							wb = new XSSFWorkbook(is);
						}
						if (wb != null) {
							result = this.readExcel(wb);
							if (!result.getBoolean("result")) {
								OBDal.getInstance().rollbackAndClose();
								return result;
							} else {
								OBDal.getInstance().commitAndClose();
								result.put("result", true);
								result.put("desc", "导入成功");
								return result;
							}
						} else {
							result.put("desc", "文件格式错误，至于许EXCEL的导入");
							return result;
						}
					} catch (Exception e) {
						OBDal.getInstance().rollbackAndClose();
						if (e.getClass().getName().contains("ConstraintViolationException")) {
							result.put("result", false);
							result.put("desc", "文件数据有问题，请仔细检查。");
						}
						e.printStackTrace();
					} finally {
						is.close();
					}
				}
			}
		} catch (Exception e) {
			OBDal.getInstance().rollbackAndClose();
			e.printStackTrace();
		}
		return result;
	}
	
	//2
		/**
		 * 读取excel记录
		 * 
		 * @param wb
		 * @return
		 * @throws Exception
		 */
		private JSONObject readExcel(Workbook wb) throws Exception {
			String meg = "", errorMsg;
			int number = wb.getNumberOfSheets();
			JSONObject result = new JSONObject();
			result.put("result", true);
			if (number <= 0) {
				result.put("result", false);
				result.put("desc", "文件格式错误。请仔细检查");
				return result;
			} else {
				// 读取第一页
				Sheet sheet = wb.getSheetAt(0);
				int lastRowNum = sheet.getLastRowNum();
				for (int i = 1; i <= lastRowNum; i++) {
					Row row = sheet.getRow(i);
					errorMsg = this.checkData(row);
					if (errorMsg != null && !"".equals(errorMsg)) {
						meg = errorMsg;
					}
					
				}
				if (!"".equals(meg)) {
					result.put("result", false);
					result.put("desc", meg);
				}
				return result;
				
			}
		}
		
		
		/**
		 * 开始存储记录
		 * 
		 * @param row
		 * @return
		 * @throws Exception
		 */
		
		private String checkData(Row row) throws Exception {
				 
				String meg = "";
				String code = readValue(row.getCell(1));  //设备编码
				String planName = readValue(row.getCell(2)); //保养计划名称
				String Organizations = readValue(row.getCell(3)); //组织机构
				String status = "CREATE"; 	//创建状态
				Boolean effective = true;			//有效
				String types= "MAINTAIN";		//类型
				
				String plancode = CodeGenerateRuleUtil.getCode("Eam_Maintain_Plan", "sparecode");	//保养计划编码
				SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
				
				Date time  =sdf.parse(sdf.format(new Date()));  //获取当前系统时间
				
				String aql = "select * from Eam_Facilities where code = ? ";
				
				EamFacilities  FacilitieIds = (EamFacilities)OBDal.getInstance().getSession().createSQLQuery(aql).addEntity(EamFacilities.ENTITY_NAME).setString(0, code).uniqueResult();    //设备ID
				
				
				String userid =	SessionInfo.getUserId(); //获取当前登录人的ad_user_id设为导入人的id
				String wql = "select * from ad_user where ad_user_id = ? ";
			
				User userids = (User)OBDal.getInstance().getSession().createSQLQuery(wql).addEntity(User.ENTITY_NAME).setString(0, userid).uniqueResult();    //责任人ID
				
				String rql ="select * from emes_process"
						+ " where emes_process_id = (select emes_process_id from emes_process_facilities  "
						+ " where Eam_Facilities_Id = (select Eam_Facilities_Id from Eam_Facilities  where code = ?)) ";
				
				
				EmesProcess processs = (EmesProcess)OBDal.getInstance().getSession().createSQLQuery(rql).addEntity(EmesProcess.ENTITY_NAME).setString(0, code).uniqueResult(); //查询设备对应的工序
				
				String uql = "select * from ad_org where name = ?";
				
				Organization orgIds  = (Organization)OBDal.getInstance().getSession().createSQLQuery(uql).addEntity(Organization.ENTITY_NAME).setString(0, Organizations).uniqueResult(); //查询组织机构的ID
				
				
				if(orgIds != null ){
						
					if(processs != null){
						
						if(FacilitieIds != null){
							
						//	if(userids != null){
								
								String sql = "select * " +
								" from Eam_Maintain byxm " +
								" left join Eam_Facilities_Maintain sbxq on byxm.Eam_Maintain_id = sbxq.Eam_Maintain_id " +
								" left join Eam_Facilities sb on sbxq.Eam_Facilities_Id = sb.Eam_Facilities_Id " +
								" where sb.code= ? ";
						
								List<EamMaintainItem> maintainids = (List<EamMaintainItem>)OBDal.getInstance().getSession().createSQLQuery(sql).addEntity(EamMaintainItem.ENTITY_NAME).setString(0, code).list();    //保养项目ID
								
								
								EamMaintainPlan  maintain = new EamMaintainPlan(); //保养计划
								
								EamMaintainPlanFacs  maintainitem = new EamMaintainPlanFacs(); //保养计划下设备
								
								EamMaintainPlanItems  planDetails = null;
								maintain.setStatus(status);
								maintain.setName(planName);
								maintain.setDistinguish(types);
								maintain.setCode(plancode);
								maintain.setUser(userids);
								maintain.setOrganization(orgIds);
								maintain.setStartDate(time);
								maintain.setActive(effective);
								OBDal.getInstance().save(maintain);
								String maintainid = maintain.getId();		//保养计划的ID
								OBDal.getInstance().flush();				//结束会话好获取到保养计划的ID
								
								
								String qql = "select * from Eam_Maintain_Plan where Eam_Maintain_Plan_id = ? ";
								
								EamMaintainPlan planids = (EamMaintainPlan)OBDal.getInstance().getSession().createSQLQuery(qql).addEntity(EamMaintainPlan.ENTITY_NAME).setString(0, maintainid).uniqueResult();
								
								maintainitem.setEamMaintainPlan(planids);
								maintainitem.setEamFacilities(FacilitieIds);
								maintainitem.setUser(FacilitieIds.getUser());
								maintainitem.setStatus(status);
								maintainitem.setActive(effective); 
								maintainitem.setEmesProcess(processs);
								OBDal.getInstance().save(maintainitem);
								String itemid = maintainitem.getId();
								OBDal.getInstance().flush();
								
								String eql = "select * from Eam_Maintain_Plan_Facs where Eam_Maintain_Plan_Facs_id = ? ";
								
								EamMaintainPlanFacs itmeids = (EamMaintainPlanFacs)OBDal.getInstance().getSession().createSQLQuery(eql).addEntity(EamMaintainPlanFacs.ENTITY_NAME).setString(0, itemid).uniqueResult();
								
								for(int i=0 ; i < maintainids.size() ; i++){
									planDetails = new EamMaintainPlanItems(); //保养计划下保养项目
		
									planDetails.setEamMaintainPlanFacs(itmeids);
									planDetails.setEamMaintainItem(maintainids.get(i));
									planDetails.setType(types);
									planDetails.setStatus(status);
									planDetails.setActive(effective);
									OBDal.getInstance().save(planDetails);
									OBDal.getInstance().flush();
								}
//							}else{
//								meg = "【第" + (row.getRowNum() + 1) + "行,保养设备责任人不存在！！";
//							}
						}else{
							meg = "【第" + (row.getRowNum() + 1) + "行,保养设备编码不存在！！";
						}
					}else{
						meg = "【第" + (row.getRowNum() + 1) + "行,保养设备无对应的工序！！";
					}
				}else{
					meg = "【第" + (row.getRowNum() + 1) + "行,组织机构不存在！！";
				}
				return meg;
				
				}
			
		//}
		//3
		// CELL_TYPE_NUMERIC 数值型 0
		// CELL_TYPE_STRING 字符串型 1
		// CELL_TYPE_FORMULA 公式型 2
		// CELL_TYPE_BLANK 空值 3
		// CELL_TYPE_BOOLEAN 布尔型 4
		// CELL_TYPE_ERROR 错误 5
		private String readValue(Cell cell) throws Exception {
			if (cell == null) {
				return "";
			}
			if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
				if (DateUtil.isCellDateFormatted(cell)) {
					return sdf.format(cell.getDateCellValue());
				}
				Double text = cell.getNumericCellValue();
				return String.valueOf(text.longValue());
			} else if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
				return cell.getStringCellValue();
			} else if (cell.getCellType() == Cell.CELL_TYPE_ERROR) {
				throw new Exception("第" + (cell.getRowIndex() + 1) + "行，" + (cell.getColumnIndex() + 1) + "列，数据格式有问题,请检查");
			} else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
				return String.valueOf(cell.getBooleanCellValue());
			} else {
				return "";
			}
		}
}